package com.jiexinyun.JdbcUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TableStructureCopier {

    /**
     * 从源数据库获取表的列信息
     */
    public static List<ColumnInfo> getTableColumns(Connection sourceConn, String schema, String tableName) throws SQLException {
        List<ColumnInfo> columns = new ArrayList<>();
        DatabaseMetaData metaData = sourceConn.getMetaData();
        String catalog = sourceConn.getCatalog();

        try (ResultSet rs = metaData.getColumns(catalog, schema, tableName, null)) {
            while (rs.next()) {
                ColumnInfo column = new ColumnInfo();
                column.setName(rs.getString("COLUMN_NAME"));
                column.setType(rs.getString("TYPE_NAME"));
                column.setSize(rs.getInt("COLUMN_SIZE"));
                column.setPrecision(rs.getInt("DECIMAL_DIGITS"));  // 注意：此处应为小数位
                column.setScale(rs.getInt("NUM_PREC_RADIX"));      // 通常为10（十进制）
                column.setNullable(rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
                columns.add(column);
            }
        }
        return columns;
    }

    /**
     * 生成目标数据库的建表 SQL（适配 PostgreSQL）
     */
    public static String generatePostgresqlDDL(String tableName, List<ColumnInfo> columns) {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE ").append(tableName).append(" (\n");

        for (int i = 0; i < columns.size(); i++) {
            ColumnInfo column = columns.get(i);
            sql.append("    ").append(column.getName()).append(" ").append(mapDataType(column));

            // 处理非空约束
            if (!column.isNullable()) {
                sql.append(" NOT NULL");
            }

            if (i < columns.size() - 1) {
                sql.append(",");
            }
            sql.append("\n");
        }
        sql.append(");");
        return sql.toString();
    }

    /**
     * 数据类型映射（SQL Server → PostgreSQL）
     */
    private static String mapDataType(ColumnInfo column) {
        String name = column.getName();
        String type = column.getType().trim();
        int size = column.getSize();
        int precision = column.getPrecision();
        int scale = column.getScale();
        boolean nullable = column.isNullable();

        /*switch (type.toLowerCase()) {
            case "int":return "INTEGER";
            case "bigint":return "BIGINT";
            case "smallint":return "SMALLINT";
            case "decimal":return String.format("NUMERIC(%d,%d)", scale, precision);
            case "numeric":return String.format("NUMERIC(%d,%d)", scale, precision);
            case "varchar":return String.format("VARCHAR(%d)",size);
            case "char":return "CHAR";
            case "datetime":return "TIMESTAMP";
            default:return type;
        }*/

        switch (type.toLowerCase()) {
            case "int":
            case "integer": return "INTEGER";
            case "bigint": return "BIGINT";
            case "smallint": return "SMALLINT";
            case "tinyint": return "SMALLINT";  // PostgreSQL没有TINYINT，用SMALLINT代替
            case "decimal":
            case "numeric": return String.format("NUMERIC(%d,%d)", scale, precision);  // 注意参数顺序
            case "varchar":
                return size == Integer.MAX_VALUE ? "TEXT" : String.format("VARCHAR(%d)", size);
            case "nvarchar":
                return size == Integer.MAX_VALUE ? "TEXT" : String.format("VARCHAR(%d)", size);
            case "char":
                return size == 1 ? "CHAR" : String.format("CHAR(%d)", size);
            case "nchar":
                return size == 1 ? "CHAR" : String.format("CHAR(%d)", size);
            case "datetime":
            case "datetime2": return "TIMESTAMP";
            case "smalldatetime": return "TIMESTAMP";
            case "date": return "DATE";
            case "time": return "TIME";
            case "datetimeoffset": return "TIMESTAMP WITH TIME ZONE";
            case "bit": return "BOOLEAN";
            case "float": return "DOUBLE PRECISION";
            case "real": return "REAL";
            case "money": return "NUMERIC(19,4)";
            case "smallmoney": return "NUMERIC(10,4)";
            case "binary":
            case "varbinary":
            case "image": return "BYTEA";
            case "uniqueidentifier": return "UUID";
            case "xml": return "XML";
            case "text":
            case "ntext": return "TEXT";
            default: return type;  // 无法识别的类型原样返回
        }

    }

    /**
     * 在目标数据库执行 DDL
     */
    public static void executeDDL(Connection targetConn, String ddl) throws SQLException {
        try (Statement stmt = targetConn.createStatement()) {
            stmt.execute(ddl);
        }
    }

}
